-- @owner: @zou_jialiang050
-- @date: 2022-05-11
-- @testpoint: insert在触发器中的使用

--step1:建表t_FVT_INSERT_0012;expect:成功
drop table if exists t_FVT_INSERT_0012;
create table t_FVT_INSERT_0012
(
    name1 varchar2(255),
    pwd1 varchar2(255),
    id varchar2(255)
) with (storage_type=ustore);

--step2:建表t_FVT_INSERT_0012_1;expect:成功
drop table if exists t_FVT_INSERT_0012_1;
create table t_FVT_INSERT_0012_1
(
    name1 varchar2(255),
    pwd1 varchar2(255),
    id varchar2(12)
) with (storage_type=ustore);

--step3:构建函数插入数据;expect:成功
create or replace function tri_insert_func() returns trigger as
$$
declare
begin
case
when inserting then
insert into t_FVT_INSERT_0012_1(name1,pwd1,id) values(:new.name1,:new.pwd1,:new.id);
when updating then
update t_FVT_INSERT_0012_1 set name1=:new.name1,pwd1=:new.pwd1 where id=:new.id;
when deleting then
delete t_FVT_INSERT_0012_1 where name1 =:old.name1;
end case;
end
$$
LANGUAGE PLPGSQL;

--step4:创建触发器;expect:成功
drop trigger if exists tr_FVT_INSERT_0012 on t_FVT_INSERT_0012;
create trigger tr_FVT_INSERT_0012
before insert or update or delete on t_FVT_INSERT_0012 for each row
execute procedure tri_insert_func();

--step5:环境清理;expect:成功
drop table t_FVT_INSERT_0012;
drop table t_FVT_INSERT_0012_1;